package com.joinval.common.utils;

import cn.hutool.core.collection.CollUtil;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.joinval.modules.sys.po.ExcelHead;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * excel处理工具
 */
@Slf4j
public class ExcelUtils {
    private static final String FULL_DATA_FORMAT = "yyyy/MM/dd  HH:mm:ss";
    private static final String SHORT_DATA_FORMAT = "yyyy/MM/dd";

    private static NumberFormat numberFormat = NumberFormat.getInstance();

    static {
        numberFormat.setGroupingUsed(false);
    }

    /**
     * Excel表头对应Entity属性 解析封装javabean
     *
     * @param classzz    类
     * @param in         excel流
     * @param fileName   文件名
     * @param excelHeads excel表头与entity属性对应关系
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads) throws Exception {
        checkFile(fileName);    // 是否EXCEL文件
        Workbook workbook = getWorkBoot(in, fileName); // 兼容新老版本
        List<T> excelForBeans = readExcel(classzz, workbook, excelHeads);  // 解析Excel
        return excelForBeans;
    }

    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, List<ExcelHead> excelHeads, int indexSheet) throws Exception {
        checkFile(fileName);    // 是否EXCEL文件
        Workbook workbook = getWorkBoot(in, fileName); // 兼容新老版本
        List<T> excelForBeans = readExcel(classzz, workbook, excelHeads, indexSheet);  // 解析Excel
        return excelForBeans;
    }

    /**
     * 解析Excel转换为Entity
     *
     * @param classzz  类
     * @param in       excel流
     * @param fileName 文件名
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName) throws Exception {
        return readExcelToEntity(classzz, in, fileName, null);
    }

    public static <T> List<T> readExcelToEntity(Class<T> classzz, InputStream in, String fileName, int indexSheet) throws Exception {
        return readExcelToEntity(classzz, in, fileName, null, indexSheet);
    }

    /**
     * 校验是否是Excel文件
     *
     * @param fileName
     * @throws Exception
     */
    public static void checkFile(String fileName) throws Exception {
        if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
            throw new Exception("不是Excel文件！");
        }
    }

    /**
     * 兼容新老版Excel
     *
     * @param in
     * @param fileName
     * @return
     * @throws IOException
     */
    private static Workbook getWorkBoot(InputStream in, String fileName) throws Exception {
//		if (fileName.endsWith(".xlsx")) {
//			return new XSSFWorkbook(in);
//		} else {
//			return new HSSFWorkbook(in);
//		}
        Workbook workbook = WorkbookFactory.create(in);
        return workbook;
    }


    /**
     * 设置Excel页脚
     */
    public static void setExcelFooterName(String customExcelFooterName, int setExcelFooterNumber, XSSFWorkbook wb) {
        wb.setSheetName(setExcelFooterNumber, customExcelFooterName);
    }

    /**
     * 输出流 导出Excel到桌面
     */
    public static void exportOutPutExcel(String fileName, XSSFWorkbook wb, HttpServletResponse response) {

        OutputStream outputStream = null;
        try {
            Date date = new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            fileName += "_" + df.format(date) + ".xlsx";
            outputStream = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            response.setContentType("application/msexcel");
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage());
        }

    }


    /**
     * 解析Excel
     *
     * @param classzz    类
     * @param workbook   工作簿对象
     * @param excelHeads excel与entity对应关系实体
     * @param <T>
     * @return
     * @throws Exception
     */
    private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {
        List<T> beans = new CopyOnWriteArrayList<T>();
        int sheetNum = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            String sheetName = sheet.getSheetName();
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();
            Row head = sheet.getRow(firstRowNum);
            if (head == null) {
                continue;
            }
            short firstCellNum = head.getFirstCellNum();
            short lastCellNum = head.getLastCellNum();
            Field[] fields = classzz.getDeclaredFields();
            for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
                Row dataRow = sheet.getRow(rowIndex);
                if (dataRow == null) {
                    continue;
                }
                T instance = classzz.newInstance();
                if (CollectionUtils.isEmpty(excelHeads)) {  // 非头部映射方式，默认不校验是否为空，提高效率
                    firstCellNum = dataRow.getFirstCellNum();
                    lastCellNum = dataRow.getLastCellNum();
                }
                for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                    Cell headCell = head.getCell(cellIndex);
                    if (headCell == null) {
                        continue;
                    }
                    Cell cell = dataRow.getCell(cellIndex);
                    headCell.setCellType(CellType.STRING);
                    String _headName = headCell.getStringCellValue().trim();
                    String headName = null;
                    // 新开业门店企业导入
                     if ("NewAccountOpenImportVo".equals(classzz.getSimpleName())) {
                         headName = exChange3(_headName);
                     } else if ("SpecialRectificationVo".equals(classzz.getSimpleName())) {
                         headName = exChange4(_headName);
                     } else if ("GasSupervisionInspectionVo".equals(classzz.getSimpleName())) {
                         headName = exChange5(_headName);
                     } else {
                         exChange(_headName);
                     }
                    if (StringUtils.isEmpty(headName)) {
                        continue;
                    }
                    ExcelHead eHead = null;
                    if (!CollectionUtils.isEmpty(excelHeads)) {
                        for (ExcelHead excelHead : excelHeads) {
                            if (headName.equals(excelHead.getExcelName())) {
                                eHead = excelHead;
                                headName = eHead.getEntityName();
                                break;
                            }
                        }
                    }
                    boolean flag = false;
                    for (Field field : fields) {
                        // 如果实体类所有的变量类型都对应不上
                        if (headName.equalsIgnoreCase(field.getName())) {
                            flag = true;
                            String methodName = MethodUtils.setMethodName(field.getName());
                            Method method = classzz.getMethod(methodName, field.getType());
                            if (isDateFied(field)) {
                                Date date = null;
                                if (cell != null) {
                                    date = cell.getDateCellValue();
                                }
                                if (date == null) {
                                    volidateValueRequired(eHead, sheetName, rowIndex);
                                    break;
                                }
                                method.invoke(instance, cell.getDateCellValue());
                            } else {
                                String value = null;
                                if (cell != null) {
//                                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//                                        value = getRealStringValueOfDouble(cell.getNumericCellValue());
//                                    } else {
//                                        cell.setCellType(Cell.CELL_TYPE_STRING);
//                                        value = cell.getStringCellValue();
//                                    }
                                    // todo 表格类型设置了无效 暂时注释
                                    cell.setCellType(CellType.STRING);
                                    value = cell.getStringCellValue();
                                }
                                if (StringUtils.isEmpty(value)) {
                                    volidateValueRequired(eHead, sheetName, rowIndex);
                                    break;
                                }
                                method.invoke(instance, convertType(field.getType(), value.trim()));
                            }
                            break;
                        }
                    }
                    if (!flag) {
                        throw new RuntimeException("请检查传入的数据格式");
                    }
                }
                beans.add(instance);
            }
        }
        return beans;
    }

    private static <T> List<T> readExcel(Class<T> classzz, Workbook workbook, List<ExcelHead> excelHeads, int sheetIndex) throws Exception {
        List<T> beans = new CopyOnWriteArrayList<T>();
//        int sheetNum = workbook.getNumberOfSheets();
//        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        String sheetName = sheet.getSheetName();
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        Row head = sheet.getRow(firstRowNum);
//            if (head == null)
//                continue;
        short firstCellNum = head.getFirstCellNum();
        short lastCellNum = head.getLastCellNum();
        Field[] fields = classzz.getDeclaredFields();
        System.out.println(classzz.getSimpleName() + "," + classzz.getCanonicalName());
        for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
            Row dataRow = sheet.getRow(rowIndex);
            if (dataRow == null)
                continue;
            T instance = classzz.newInstance();
            if (CollectionUtils.isEmpty(excelHeads)) {  // 非头部映射方式，默认不校验是否为空，提高效率
                firstCellNum = dataRow.getFirstCellNum();
                lastCellNum = dataRow.getLastCellNum();
            }
            for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                Cell headCell = head.getCell(cellIndex);
                if (headCell == null)
                    continue;
                Cell cell = dataRow.getCell(cellIndex);
                headCell.setCellType(CellType.STRING);
                String _headName = headCell.getStringCellValue().trim();
                String headName = null;

                if ("ExcelImportVo".equals(classzz.getSimpleName())) {
                    headName = exChange1(_headName);
                } else if ("ExcelImportMerVo".equals(classzz.getSimpleName())) {
                    headName = exChange2(_headName);
                } else if ("NewAccountOpenImportVo".equals(classzz.getSimpleName())) {
                    headName = exChange3(_headName);
                } else {
                    headName = exChange(_headName);
                }

                if (StringUtils.isEmpty(headName)) {
                    continue;
                }
                ExcelHead eHead = null;
                if (!CollectionUtils.isEmpty(excelHeads)) {
                    for (ExcelHead excelHead : excelHeads) {
                        if (headName.equals(excelHead.getExcelName())) {
                            eHead = excelHead;
                            headName = eHead.getEntityName();
                            break;
                        }
                    }
                }
                boolean flag = false;
                for (Field field : fields) {
                    // 如果实体类所有的变量类型都对应不上
                    if (headName.equalsIgnoreCase(field.getName())) {
                        flag = true;
                        String methodName = MethodUtils.setMethodName(field.getName());
                        Method method = classzz.getMethod(methodName, field.getType());
                        if (isDateFied(field)) {
                            Date date = null;
                            if (cell != null) {
                                date = cell.getDateCellValue();
                            }
                            if (date == null) {
                                volidateValueRequired(eHead, sheetName, rowIndex);
                                break;
                            }
                            method.invoke(instance, cell.getDateCellValue());
                        } else {
                            String value = null;
                            if (cell != null) {
//                                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//                                        value = getRealStringValueOfDouble(cell.getNumericCellValue());
//                                    } else {
//                                        cell.setCellType(Cell.CELL_TYPE_STRING);
//                                        value = cell.getStringCellValue();
//                                    }
                                // todo 表格类型设置了无效 暂时注释
                                cell.setCellType(CellType.STRING);
                                value = cell.getStringCellValue();
                            }
                            if (StringUtils.isEmpty(value)) {
                                volidateValueRequired(eHead, sheetName, rowIndex);
                                break;
                            }
                            method.invoke(instance, convertType(field.getType(), value.trim()));
                        }
                        break;
                    }
                }
                if (!flag) {
                    throw new RuntimeException("请检查传入的文件格式");
                }
            }
            beans.add(instance);
        }
//        }
        return beans;
    }

    private static String exChange1(String headName) {
        headName = headName.replace(" ", "");
        if (headName.contains("网点编号")) {
            return "no";
        } else if (headName.contains("网点地址")) {
            return "address";
        } else if (headName.contains("名称")) {
            return "name";
        } else if (headName.contains("所属街镇")) {
            return "town";
        } else if (headName.contains("经营情况（经营或空关或自用）")) {
            return "bus";
        } else if (headName.contains("面积")) {
            return "area";
        } else if (headName.contains("管理员")) {
            return "manager";
        } else if (headName.contains("网点类型（普通网点或社区商业）")) {
            return "type";
        }
        return headName;
    }

    private static String exChange2(String headName) {
        headName = headName.replace(" ", "");
        headName = headName.replace(" ", "");
        if (headName.contains("商户编号")) {
            return "no";
        } else if (headName.contains("商户名称")) {
            return "name";
        } else if (headName.contains("商户简称")) {
            return "merShort";
        } else if (headName.contains("经营类别")) {
            return "businessType";
        } else if (headName.contains("商户简介")) {
            return "intro";
        } else if (headName.contains("网店编号")) {
            return "wdbh";
        } else if (headName.contains("门头照片文件名")) {
            return "pic";
        } else if (headName.contains("服务清单")) {
            return "serviceItem";
        } else if (headName.contains("所属网点")) {
            return "sswd";
        }
        return headName;
    }

    /**
     * 从double数据中获取真实的String值
     *
     * @return: java.lang.String
     * @date: 2020/5/8 5:32 下午
     * @auther: chenkaihang
     */
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean containsE = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (containsE) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
//            DecimalFormat df = new DecimalFormat("#.00");
//            doubleStr = df.format(d);

//            java.util.regex.Pattern p = Pattern.compile(".0$");
//            java.util.regex.Matcher m = p.matcher(doubleStr);
            // "^(([1-9]\\d*)|0)(\\.\\d{1})?$"
            Pattern p1 = Pattern.compile("^(([1-9])|0)(\\.\\d{1})?$");
            Matcher m1 = p1.matcher(doubleStr);

            Pattern p2 = Pattern.compile("^((-[1-9])|0)(\\.\\d{1})?$");
            Matcher m2 = p2.matcher(doubleStr);

            Pattern p3 = Pattern.compile("^-[0]+(\\.\\d{2})?$");
            Matcher m3 = p3.matcher(doubleStr);
//            if (m.find()) {
//                doubleStr = doubleStr.replace(".0", ".00");
//            }
            if (m1.find() || m2.find() || m3.find()) {
                doubleStr = doubleStr + "0";
            }


        }
        return doubleStr;
    }

    private static String exChange3(String headName) {
        headName = headName.replace(" ", "");
        if (headName.contains("客户名称")) {
            return "enterpriseName";
        } else if (headName.contains("地址")) {
            return "officeAddress";
        } else if (headName.contains("所属街道")) {
            return "town";
        } else if (headName.contains("联系电话")) {
            return "legalPersonPhone";
        } else if (headName.contains("开户日期")) {
            return "accountOpenDate";
        } else if (headName.contains("身份证号")) {
            return "idCard";
        }
        return headName;
    }

    private static String exChange4(String headName) {
        headName = headName.replace(" ", "");
        if (headName.contains("企业名称")) {
            return "enterpriseName";
        } else if (headName.contains("所属区")) {
            return "county";
        } else if (headName.contains("所属街镇")) {
            return "town";
        }else if (headName.contains("办公地址")) {
            return "officeAddress";
        }else if (headName.contains("统一社会信用代码")) {
            return "uscCode";
        } else if (headName.contains("法人代表")) {
            return "legalPerson";
        } else if (headName.contains("法人电话")) {
            return "legalPersonPhone";
        } else if (headName.contains("联系人")) {
            return "contactPerson";
        } else if (headName.contains("联系电话")) {
            return "contactNumber";
        }else if (headName.contains("用气类型（天燃气/液化气）")) {
            return "gasType";
        }
        return headName;
    }

    private static String exChange5(String headName) {
        headName = headName.replace(" ", "");
        if (headName.contains("检查时间")) {
            return "checkTime";
        } else if (headName.contains("所在区")) {
            return "county";
        } else if (headName.contains("企业名称")) {
            return "enterpriseName";
        } else if (headName.contains("企业地址")) {
            return "officeAddress";
        } else if (headName.contains("用气类型")) {
            return "gasType";
        } else if (headName.contains("检查事项")) {
            return "checkItem";
        } else if (headName.contains("隐患描述")) {
            return "hazardDescription";
        }
        return headName;
    }
    private static String exChange(String headName) {
        headName = headName.replace(" ", "");
        if (headName.contains("企业名称")) {
            return "enterpriseName";
        } else if (headName.contains("区属")) {
            return "district";
        } else if (headName.contains("街镇")) {
            return "town";
        } else if (headName.contains("办公地址")) {
            return "officeAddress";
        } else if (headName.contains("统一社会信用代码")) {
            return "uscCode";
        } else if (headName.contains("法人代表")) {
            return "legalPerson";
        } else if (headName.contains("联系电话")) {
            return "legalPersonPhone";
        } else if (headName.contains("用气类型")) {
            return "gasType";
        } else if (headName.contains("来源")) {
            return "source";
        }
        return headName;
    }

    /**
     * 是否日期字段
     *
     * @param field
     * @return
     */
    private static boolean isDateFied(Field field) {
        return (Date.class == field.getType());
    }

    /**
     * 空值校验
     *
     * @param excelHead
     * @throws Exception
     */
    private static void volidateValueRequired(ExcelHead excelHead, String sheetName, int rowIndex) throws Exception {
        if (excelHead != null && excelHead.isRequired()) {
            throw new Exception("《" + sheetName + "》第" + (rowIndex + 1) + "行:\"" + excelHead.getExcelName() + "\"不能为空！");
        }
    }

    /**
     * 类型转换
     *
     * @param classzz
     * @param value
     * @return
     */
    private static Object convertType(Class classzz, String value) {
        if (Integer.class == classzz || int.class == classzz) {
            return Integer.valueOf(value);
        }
        if (Short.class == classzz || short.class == classzz) {
            return Short.valueOf(value);
        }
        if (Byte.class == classzz || byte.class == classzz) {
            return Byte.valueOf(value);
        }
        if (Character.class == classzz || char.class == classzz) {
            return value.charAt(0);
        }
        if (Long.class == classzz || long.class == classzz) {
            return Long.valueOf(value);
        }
        if (Float.class == classzz || float.class == classzz) {
            return Float.valueOf(value);
        }
        if (Double.class == classzz || double.class == classzz) {
            return Double.valueOf(value);
        }
        if (Boolean.class == classzz || boolean.class == classzz) {
            return Boolean.valueOf(value.toLowerCase());
        }
        if (BigDecimal.class == classzz) {
            return new BigDecimal(value);
        }
       /* if (Date.class == classzz) {
            SimpleDateFormat formatter = new SimpleDateFormat(FULL_DATA_FORMAT);
            ParsePosition pos = new ParsePosition(0);
            Date date = formatter.parse(value, pos);
            return date;
        }*/
        return value;
    }


    /**
     * 获取properties的set和get方法
     */
    static class MethodUtils {
        private static final String SET_PREFIX = "set";
        private static final String GET_PREFIX = "get";

        private static String capitalize(String name) {
            if (name == null || name.length() == 0) {
                return name;
            }
            return name.substring(0, 1).toUpperCase() + name.substring(1);
        }

        public static String setMethodName(String propertyName) {
            return SET_PREFIX + capitalize(propertyName);
        }

        public static String getMethodName(String propertyName) {
            return GET_PREFIX + capitalize(propertyName);
        }
    }

    // 判断该对象除了month是否都为null，是返回false
//    public static boolean isAllFieldNull(PbqAvgDto pbqAvgDto) {
//        if (!StringUtils.isEmpty(pbqAvgDto.getAvgFat())) {
//            return true;
//        }
//        if (!StringUtils.isEmpty(pbqAvgDto.getAvgFreezingPoint())) {
//            return true;
//        }
//        if (!StringUtils.isEmpty(pbqAvgDto.getAvgProtein())) {
//            return true;
//        }
//        if (!StringUtils.isEmpty(pbqAvgDto.getAvgSomaticCell())) {
//            return true;
//        }
//        if (!StringUtils.isEmpty(pbqAvgDto.getAvgTotalColonies())) {
//            return true;
//        }
//        return false;
//
//    }

    /**
     * 生成图片
     *
     * @param workbook  工作簿
     * @param patriarch 工作表
     * @param bytes     文件元数据列表
     * @param hang      第几行
     * @param lie      设置图片位于表格的单元格下标
     */
    public static void generatePicture(HSSFWorkbook workbook, HSSFPatriarch patriarch, List<byte[]> bytes, int hang,int lie) throws IOException {
        BufferedImage bufferedImage = null;
        if (CollUtil.isEmpty(bytes)) {
            return;
        }
        for (int i = 0; i < bytes.size(); i++) {
            // 计算边距
            int mar = 10 + 10 + (bytes.size() - 1) * 10;
            // 大致平均值,每个图片宽度(1023为每个单元格总比，）
            int ave = (1023 - mar) / bytes.size();
            byte[] bytes1 = bytes.get(i);

            try {
                bufferedImage = ImageIO.read(new ByteArrayInputStream(bytes1));

                if (ObjectUtils.isEmpty(bufferedImage)) {
                    log.warn("读取文件异常");
                    continue;
                }
            } catch (IOException e) {
                log.error("读取文件异常：异常信息是：{}", e.getMessage());
                continue;
            }
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

            try {
                ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
            } catch (IOException e) {
                log.error("===ImageIO.write==,异常信息：{}", e.getMessage());
                continue;
            }
            /* dx1:图片左边界距离单元格左边框像素值,
             * dy1:图片上边界距离单元格上边框像素值,
             * dx2:图片右边界距离单元格右边框像素值（负数）,
             * dy2:图片下边界距离单元格下边框像素值（负数）,
             * col1:列下标（0开始），
             * row1:行下标（0开始），
             * col2:列下标（1开始），
             * row2:行下标（1开始）。*/
            HSSFClientAnchor anchor = new HSSFClientAnchor(10 * (i + 1) + ave * i, 10, (10 + ave) * (i + 1), 245, (short) lie, hang, (short) lie, hang);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOutputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            byteArrayOutputStream.close();
        }
    }
}

